﻿CREATE PROCEDURE [dbo].[spMomPortInitial] ( @OwnerID int )
as
begin
	set nocount on

	declare @message nvarchar(1000)
	set @message = 'MOMPortInitial' + '  ' + convert( varchar, getdate(), 9 )
	RAISERROR (@message, 0, 1) WITH NOWAIT
	declare @SourceCode varchar(3)
	set @SourceCode = 'GC'

	declare @custnumCode char(1)
	set @custnumCode = 'C'
	insert into codeIDSource ( IDSourceCode, OwnerID, Description )
	select @custnumCode, @OwnerID, @SourceCode + ' - MOM custnum'
	where not exists ( select 1 from codeIDSource where OwnerID = @OwnerID and IDSourceCode = @custnumCode )

	declare @altnumCode char(1)
	set @altnumCode = 'D'
	insert into codeIDSource ( IDSourceCode, OwnerID, Description )
	select @altnumCode, @OwnerID, @SourceCode + ' - MOM altnum'
	where not exists ( select 1 from codeIDSource where OwnerID = @OwnerID and IDSourceCode = @altnumCode )

	declare @orderCode char(1)
	set @orderCode = 'E'
	insert into codeIDSource ( IDSourceCode, OwnerID, Description )
	select @orderCode, @OwnerID, @SourceCode + ' - MOM order'
	where not exists ( select 1 from codeIDSource where OwnerID = @OwnerID and IDSourceCode = @orderCode )

	declare @alt_ordCode char(1)
	set @alt_ordCode = 'F'
	insert into codeIDSource ( IDSourceCode, OwnerID, Description )
	select @alt_ordCode, @OwnerID, @SourceCode + ' - MOM alt_ord'
	where not exists ( select 1 from codeIDSource where OwnerID = @OwnerID and IDSourceCode = @alt_ordCode )

	insert into tblPromotion( Description, OwnerID, Active )
	select distinct adin, @OwnerID, 1
	from openquery(MOMPort, 'select * from adcosts') T1	
		left outer join tblPromotion T2 on T1.adin = T2.description

	 
	-- Create the CarrierCode values
	declare @startChar int
	select @startChar = coalesce(max(ascii(CarrierCode)), 97) from codeCarrier

	insert into codeCarrier ( CarrierCode, Abbrev, Description )
	select char(@startChar + row_number() over (order by ca_code)),
		ca_code, ca_title
	from openquery( MOMPort, 'select * from carrier') T1
		left outer join codeCarrier T2 on T1.ca_code = T2.abbrev
	where ca_code <> 'NS'
		and T2.CarrierCode is null

	-- Create the default product group
	declare @ProductGroupID int
	insert into tblProductGroup 
		(ShortName, LongName, OwnerID, Active )
	select 'Default', 'Default', @OwnerID, 1
	set @ProductGroupID = SCOPE_IDENTITY()

	-- Create the product records
	insert into tblProduct (ProductGroupID, ItemCode, ShortName, LongName, Description, 
		InvoiceDesc, WebDescription, ActiveItem, InventoryItem, SalableItem,
		AutoBreakdown, AutoBuild, Oversize, MaxPerHousehold, Color, Size, Weight,
		PreferredCarrierCode, OwnerID )
	select @ProductGroupID, T1.[number], left(T1.[desc], 20), 
		T1.[desc] + char(13) + T1.[desc2], T1.[desc] + char(13) + T1.[desc2],
		T1.[desc] + char(13) + T1.[desc2], T1.[inetsdesc], 1, 1 - T1.[nonproduct], 1,
		0, T1.Construct, T1.oversize, 0, '', '', T1.[unitweight] * 16,
		T2.CarrierCode, @OwnerID
	from openquery( MOMPort, 'select * from stock' ) as T1
		left outer join codeCarrier T2 on T1.Carrier = T2.Abbrev

	insert into tblProductComponent ( ProductID, ComponentProductID, Qty )
	select T2.ProductID, T3.ProductID, max(T1.q)
	from openquery( MOMPort, 'select * from new' ) as T1
		join tblProduct T2 on T1.prod = T2.ItemCode and T2.OwnerID = @OwnerID
		join tblProduct T3 on T1.inv = T3.ItemCode and T3.OwnerID = @OwnerID
	group by T2.ProductID, T3.ProductID

	declare @LocationID int
	select top 1 @LocationID = LocationID from tblLocation where LocationTypeCode = 'W'

	-- Create the inventory
	insert into tblInventory ( LocationID, ProductID, Qty )
	select @LocationID, T2.ProductID, case when T1.units > 0 then T1.units else 0 end
	from openquery( MOMPort, 'select number, units from stock' ) as T1
		join tblProduct T2 on T1.number = T2.itemcode
			and T2.ownerid = @OwnerID

	-- Create the tax zones
	insert into tblTaxZone ( Name ) 
	select T1.name 
	from openquery( MOMPort, 'select * from state where taxrate <> 0' ) as T1
		left outer join tblTaxZone T2 on T1.name = T2.name
	where T2.name is null
	insert into tblTaxZone ( Name ) 
	select rtrim(T1.name) + ' ' + T1.state
	from openquery( MOMPort, 'select * from County where ctaxr <> 0' ) as T1
		left outer join tblTaxZone T2 on rtrim(T1.name) + ', ' + T1.state = T2.name
	where T2.name is null
	select rtrim(T1.city) + ', ' + T1.state
	from openquery( MOMPort, 'select * from zip where itaxr <> 0' ) as T1
		left outer join tblTaxZone T2 on rtrim(T1.city) + ', ' + T1.state = T2.name
	where T2.name is null

	-- Create the tax accounts for this owner
	insert into tblAccount ( CreateDate, Name, AccountTypeCode, OwnerID, CurrencyID )
	select getdate(), T1.Name + ' Tax', 'E', @OwnerID, 1 
	from openquery( MOMPort, 'select * from state where taxrate <> 0' ) as T1
		left outer join tblTaxZone T2 on T1.name = T2.name
	where T2.name is null
	insert into tblAccount ( CreateDate, Name, AccountTypeCode, OwnerID, CurrencyID )
	select getdate(), rtrim(T1.name) + ' ' + T1.state + ' Tax', 'E', @OwnerID, 1 
	from openquery( MOMPort, 'select * from County where ctaxr <> 0' ) as T1
		left outer join tblTaxZone T2 on rtrim(T1.name) + ', ' + T1.state = T2.name
	where T2.name is null
	insert into tblAccount ( CreateDate, Name, AccountTypeCode, OwnerID, CurrencyID )
	select getdate(), rtrim(T1.city) + ', ' + T1.state + ' Tax', 'E', @OwnerID, 1 
	from openquery( MOMPort, 'select * from zip where itaxr <> 0' ) as T1
		left outer join tblTaxZone T2 on rtrim(T1.city) + ', ' + T1.state = T2.name
	where T2.name is null

	-- Link the tax zones with postal codes
	insert into tblPostalCodeTaxZone ( PostalCodeID, TaxZoneID )
	select T3.PostalCodeID, T2.TaxZoneID
	from openquery( MOMPort, 'select * from state where taxrate <> 0' ) as T1
		join tblTaxZone T2 on T1.name = T2.name
		join tblPostalCode T3 on T1.State = T3.State and T3.CountryID = 1
		left outer join tblPostalCodeTaxZone T4 on T3.PostalCodeID = T4.PostalCodeID
			and T2.TaxZoneID = T4.TaxZoneID
	where T4.PostalCodeTaxZoneID is null
	insert into tblPostalCodeTaxZone ( PostalCodeID, TaxZoneID )
	select T3.PostalCodeID, T2.TaxZoneID
	from openquery( MOMPort, 'select * from County where ctaxr <> 0' ) as T1
		join tblTaxZone T2 on rtrim(T1.name) + ', ' + T1.state = T2.name
		join tblPostalCode T3 on T1.State = T3.State and T1.name = T3.County and T3.CountryID = 1
		left outer join tblPostalCodeTaxZone T4 on T3.PostalCodeID = T4.PostalCodeID
			and T2.TaxZoneID = T4.TaxZoneID
	where T4.PostalCodeTaxZoneID is null
	insert into tblPostalCodeTaxZone ( PostalCodeID, TaxZoneID )
	select T3.PostalCodeID, T2.TaxZoneID
	from openquery( MOMPort, 'select * from zip where itaxr <> 0' ) as T1
		join tblTaxZone T2 on rtrim(T1.city) + ', ' + T1.state = T2.name
		join tblPostalCode T3 on T1.State = T3.State and T1.City = T3.City and T3.CountryID = 1
		left outer join tblPostalCodeTaxZone T4 on T3.PostalCodeID = T4.PostalCodeID
			and T2.TaxZoneID = T4.TaxZoneID
	where T4.PostalCodeTaxZoneID is null

	-- Create the tax rates
	insert into tblTaxRate ( Name, TaxZoneID, EffectiveDate, Rate, TaxShipping, DefaultRate )
	select rtrim(T1.State) + ' Default', T2.TaxZoneID, '1900-1-1', T1.taxrate / 100, TaxShip, 1
	from openquery( MOMPort, 'select * from state where taxrate <> 0' ) as T1
		join tblTaxZone T2 on T1.name = T2.name
		left outer join tblTaxRate T3 on rtrim(T1.State) + ' Default' = T3.Name
	where T3.Name is null
	insert into tblTaxRate ( Name, TaxZoneID, EffectiveDate, Rate, TaxShipping, DefaultRate )
	select rtrim(T1.county) + ', ' + rtrim(T1.State) + ' Default', T2.TaxZoneID, '1900-1-1', T1.ctaxr / 100, TaxShip, 1
	from openquery( MOMPort, 'select * from County where ctaxr <> 0' ) as T1
		join tblTaxZone T2 on T1.name + ', ' + T1.State = T2.name
		left outer join tblTaxRate T3 on rtrim(T1.county) + ', ' + rtrim(T1.State) + ' Default' = T3.Name
	where T3.Name is null
	insert into tblTaxRate ( Name, TaxZoneID, EffectiveDate, Rate, TaxShipping, DefaultRate )
	select distinct rtrim(T1.City) + ', ' + rtrim(T1.State) + ' Default', T2.TaxZoneID, '1900-1-1', T1.itaxr / 100, TaxShip, 1
	from openquery( MOMPort, 'select * from zip where itaxr <> 0' ) as T1
		join tblTaxZone T2 on rtrim(T1.city) + ', ' + rtrim(T1.State) = T2.name
		left outer join tblTaxRate T3 on rtrim(T1.city) + ', ' + rtrim(T1.State) + ' Default' = T3.Name
	where T3.Name is null

	-- Link the owner to the tax rates
	insert into tblOwnerTaxRate( OwnerID, TaxRateID )
	select @OwnerID, T2.TaxRateID
	from openquery( MOMPort, 'select * from state where taxrate <> 0' ) as T1
		join tblTaxRate T2 on rtrim(T1.State) + ' Default' = T2.Name
		left outer join tblOwnerTaxRate T3 on T2.TaxRateID = T3.TaxRateID and T3.OwnerID = @OwnerID
	where T3.TaxRateID is null
	insert into tblOwnerTaxRate( OwnerID, TaxRateID )
	select @OwnerID, T2.TaxRateID
	from openquery( MOMPort, 'select * from county where ctaxr <> 0' ) as T1
		join tblTaxRate T2 on rtrim(T1.name) + ', ' + RTRIM(T1.state) + ' Default' = T2.Name
		left outer join tblOwnerTaxRate T3 on T2.TaxRateID = T3.TaxRateID and T3.OwnerID = @OwnerID
	where T3.TaxRateID is null
	insert into tblOwnerTaxRate( OwnerID, TaxRateID )
	select @OwnerID, T2.TaxRateID
	from openquery( MOMPort, 'select * from zip where itaxr <> 0' ) as T1
		join tblTaxRate T2 on rtrim(T1.city) + ', ' + RTRIM(T1.state) + ' Default' = T2.Name
		left outer join tblOwnerTaxRate T3 on T2.TaxRateID = T3.TaxRateID and T3.OwnerID = @OwnerID
	where T3.TaxRateID is null

end
